package com.owen.he.jee.service.subService;

import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.concurrent.atomic.AtomicInteger;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.owen.he.jee.model.CellTemplate;
import com.owen.he.jee.model.ExportTemplate;
import com.owen.he.jee.model.Student;
import com.owen.he.jee.service.exception.Assert;
import com.owen.he.jee.service.exception.ServiceException;
import com.owen.he.jee.service.impl.excel.XMLFactory;
import com.owen.he.jee.utils.ExcelRelation;
import com.owen.he.jee.utils.FileUtil;
import com.owen.he.jee.utils.excel.ExcelFileUtils;
import com.owen.he.jee.vo.ResultJsonMsg;

/**
 * 导出excel通用接口封装
 * @author owen.he
 */
@Service
public class ExcelExportService {
	
	private static final Logger logger = LoggerFactory.getLogger(ExcelExportService.class);
	
	/** excel导出临时文件目录  */
	@Value("${excel.export.excel.data.temp.dir}")
    private String excelExportExcelDataTempDir;
	
	/** excel导出模板文件目录 */
	@Value("${excel.export.template.temp.dir}")
    private String excelExportExcelTemplateTempDir;
	
	/** 初始列 */
	private static final int DEAULT_START_COLUMN_NUMBER = 0;
	
	/**
	 * 导出excel前数据封装 
	 * 1.根据文件名解析xml文件，获取excel模板对象，包含excel的基本属性
	 * 2.将数据一列列的存入BatchExportInvoker对象中
	 * 3.执行下载
	 * 注：数据集合规范
	 * @param excelTemplateName 导出excel数据格式，信息配置到xml文件中
	 * @param datas 要导出的数据，此数据只支持List<Map<String, Object> 类型
	 * @param response 下载excel文件的消息头
	 */
	@SuppressWarnings({ "static-access", "rawtypes" })
	public void doBeforeExport(final String excelTemplateName, final Iterator datas, final HttpServletResponse response) {
		//获取excel的导出模板
		String exportTemplateFilePath = excelExportExcelTemplateTempDir + excelTemplateName+ ".xml";
		logger.info("解析excel导出模板,文件路径path:{}", exportTemplateFilePath);
		//获取导出模板对象
		ExportTemplate template = parseExcelXmlToBean(exportTemplateFilePath);
		//验证模板格式是否正确
		validExcelTemplate(template);
		final int initRowNumber = 1;
        final AtomicInteger rowNumber = new AtomicInteger(initRowNumber); /** 用于显示的行号 */
        //表示是否是最后一行
        final AtomicBoolean lastRow = new AtomicBoolean(false);
		if(template!=null) {
			/** 封装导出excel的内容&执行下载文件 */
			doExport(template, new BatchExportInvoker() {
				
				@Override
				public Map<String, Object> next() {
					@SuppressWarnings("unchecked")
					Map<String, Object> rowDataMap = (Map<String, Object>) datas.next();
					return rowDataMap;
				}
				
				@Override
				public boolean hasNext() {
					boolean hasNextRow = datas.hasNext();
					boolean notFirstRow = rowNumber.get() > initRowNumber;
					if (hasNextRow == false && notFirstRow == true)
	                      lastRow.set(true);
	                return hasNextRow || notFirstRow;
				}
				
			}, new DownloadFileInvoker() {
				/** 下载处理IO流  */
				@Override
				public void invoke(String filePath) {
					try {
						//设置下载response的头消息信息
						setResponseHeader(response, FileUtil.getFileName(filePath));
						//处理IO流
						InputStream in = new FileInputStream(filePath);
						OutputStream out = response.getOutputStream();
						byte[] b = new byte[2408];
						int len;
						while ((len = in.read(b))>0) {
							out.write(b, 0, len);
						}
						//关闭流
						out.close();
						in.close();
					} catch (Exception e) {
						e.printStackTrace();
					} finally {
						FileUtil.deleteDirectory(filePath);
					}
				}
			});
			
		}else {
			logger.error("获取[{}]为模板为空，导出失败", excelTemplateName);
		}
		
	}
	
	/**
	 * excel导出通用方法
	 * 1.将导出标题写到excel临时文件
	 * 2.将要导出的集合数据写到临时文件
	 * 3.将临时文件写出文件到磁盘
	 * 4.执行回调接口下载
	 * @param template excel导出模板
	 * @param exportInvoker 要下载的数据集合
	 * @param downloadFileInvoker 下载回调接口
	 */
	public void doExport(ExportTemplate template, BatchExportInvoker exportInvoker, DownloadFileInvoker downloadFileInvoker) {
		logger.info("开始执行导出,文件名为excelName:{}", template.getExcelName());
		/** Excel对象 -1:手动flush数据到磁盘 */
        SXSSFWorkbook workbook = new SXSSFWorkbook(-1);
        /** Excel.sheet对象 */
        Sheet sheet = workbook.createSheet();
		/** 自增控制excle行 */
        AtomicInteger excelRowNum = new AtomicInteger(0);
		//1.将标题写到excel临时文件中
		logger.info("开始---导出标题到临时文件中");
		doExportTitlesToTempFile(workbook, sheet, excelRowNum.getAndIncrement(), template);
		//2.导出数据到临时文件中
		logger.info("开始---导出数据到临时文件中");
		doExportDatasToTempFile(workbook, sheet, excelRowNum, template, exportInvoker);
		//3.写出文件到磁盘
		String excelPath = doExportWriteExcelFile(template.getExcelName(), workbook);
		//4.执行回调接口下载
		downloadFileInvoker.invoke(excelPath);
	}
	
	/***********************导出二次改版start***************************/
	/**
	 * @param clazz 实体对象，包含单元格excel信息
	 * @param excelName excel名字
	 * @param exportInvoker
	 * @param downloadStreamInvoker
	 */
	public void doExport(Class<?> clazz, String excelName ,BatchExportInvoker exportInvoker, DownloadStreamInvoker downloadStreamInvoker) {
		logger.info("开始执行导出");
		/** Excel对象 -1:手动flush数据到磁盘 */
        SXSSFWorkbook workbook = new SXSSFWorkbook(-1);
        /** Excel.sheet对象 */
        Sheet sheet = workbook.createSheet();
		/** 自增控制excle行 */
        AtomicInteger excelRowNum = new AtomicInteger(0);
		//1.将标题写到excel临时文件中
		logger.info("开始---导出标题到临时文件中");
		doExportTitlesToTempFile(clazz, workbook, sheet, excelRowNum.getAndIncrement());
		//2.导出数据到临时文件中
		logger.info("开始---导出数据到临时文件中");
		doExportDatasToTempFile(clazz, workbook, sheet, excelRowNum, exportInvoker);
		//3.写出文件到磁盘
		downloadStreamInvoker.invoke(workbook);
	}
	
	
	/**
	 * @param clazz
	 * @param workbook
	 * @param sheet
	 * @param excelRowNum
	 * @param exportInvoker
	 */
	private void doExportDatasToTempFile(Class<?> clazz, SXSSFWorkbook workbook, Sheet sheet, AtomicInteger excelRowNum,
			BatchExportInvoker exportInvoker) {
		List<ExcelRelation> excelRelation = ExcelFileUtils.readAnnoExportTitleNameField(clazz);
		Assert.notNull(excelRelation, "导出excel对象格式不正确!");
		while (exportInvoker.hasNext()) {
			Map<String,Object> dataMap = exportInvoker.next();
			Row row = sheet.createRow(excelRowNum.getAndIncrement());
			int colNum = DEAULT_START_COLUMN_NUMBER;//列的索引号
			XSSFCellStyle cellStyle = null;
			XSSFColor borderColor = null;
			for (ExcelRelation temp : excelRelation) {
				String field = temp.getField();
				String style = temp.getColor();
				Object value = dataMap.get(field);
				Cell cell = row.createCell(colNum++);
				//设置单元格的背景色
				if(StringUtils.isNotBlank(style)) {
					XSSFColor foregroundColor = setCellForeroundColorConfig(style);
					//获取excel的样式对象
					cellStyle = getCellStyle(workbook, borderColor, foregroundColor);
					cell.setCellStyle(cellStyle);
				}
				//设置单元格的值
				if (value != null) {
                    if (value instanceof String) {
                        if (StringUtils.isBlank((String) value)) {
                            cell.setCellValue("-");
                        } else {
                            cell.setCellValue((String) value);
                        }
                    } else {
                        cell.setCellValue(value.toString());
                    }
                } else {
                    cell.setCellValue("-");
                }
				
			}
			
		}
		
	}

	/**
	 * @param clazz
	 * @param workbook
	 * @param sheet
	 * @param rowNum
	 */
	private void doExportTitlesToTempFile(Class<?> clazz, SXSSFWorkbook workbook, Sheet sheet, int rowNum) {
		//获取标题
		List<ExcelRelation> excelRelation = ExcelFileUtils.readAnnoExportTitleNameField(clazz);
		Assert.notNull(excelRelation, "导出excel对象格式不正确!");
		/** 初始化行&单元格 */
		Row row = sheet.createRow(rowNum);
        Cell cell;
        XSSFColor borderColor = null;
        int colNum = DEAULT_START_COLUMN_NUMBER;//列的索引号
        XSSFCellStyle cellStyle;
        Assert.isTrue(excelRelation.size()!=0, "导出excel对象格式不正确!");
        for (ExcelRelation excel : excelRelation) {
			String name = excel.getName();
			String style = excel.getColor();
			//获取单元格的样式
			XSSFColor foregroundColor = setCellForeroundColorConfig(style);
			//获取excel的样式对象
			cellStyle = getCellStyle(workbook, borderColor, foregroundColor);
			//创建单元格&设置样式&赋值
            cell = row.createCell(colNum++);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(name);
		}
        try {
			((SXSSFSheet) sheet).flushRows();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/***********************导出二次改版end***************************/

	/***********************导出三次改版start***************************/
	/**
	 * @param clazz 实体对象，包含单元格excel信息
	 * @param excelName excel名字
	 * @param exportInvoker
	 * @param downloadFileInvoker
	 */
	public void doExport(Class<?> clazz, String excelName ,BatchExportInvoker exportInvoker, DownloadFileInvoker downloadFileInvoker) {
		logger.info("开始执行导出");
		/** Excel对象 -1:手动flush数据到磁盘 */
        SXSSFWorkbook workbook = new SXSSFWorkbook(-1);
        /** Excel.sheet对象 */
        Sheet sheet = workbook.createSheet();
		/** 自增控制excle行 */
        AtomicInteger excelRowNum = new AtomicInteger(0);
		//1.将标题写到excel临时文件中
		logger.info("开始---导出标题到临时文件中");
		doExportTitlesToTempFile(clazz, workbook, sheet, excelRowNum.getAndIncrement());
		//2.导出数据到临时文件中
		logger.info("开始---导出数据到临时文件中");
		doExportDatasToTempFile(clazz, workbook, sheet, excelRowNum, exportInvoker);
		//3.写出文件到磁盘
		String excelPath = doExportWriteExcelFile(excelName, workbook);
		//4.执行回调接口下载
		downloadFileInvoker.invoke(excelPath);
	}
	/***********************导出三次改版end***************************/

	
	
	
	
	
	/**
	 * 获取xml文件将其转化成ExcelDataBean
	 * @param xmlPath xml文件路径
	 * @return
	 * @author owen.he
	 */
	public ExportTemplate parseExcelXmlToBean(String xmlPath) {
		logger.info("the xml file path, param:{}", xmlPath);
		XMLFactory xmlf = new XMLFactory(ExportTemplate.class);
		try {
			//根据xml文件路径转化为ExportExcelData对象
			@SuppressWarnings("static-access")
			ExportTemplate data = xmlf.xmlFileToBean(xmlPath, ExportTemplate.class);
			if(data!=null) {
				return data;
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("para xmlFileToBean error, message:{}", e.getMessage());
		}
		return null;
	}
	
	/**
	 * 校验excel模板格式关键字段是否正确
	 * @param excel
	 * @return 
	 * 使用断言，抛出异常说明格式有问题
	 */
	public boolean validExcelTemplate(ExportTemplate excel) {
		Assert.notNull(excel, "excel模板不能为空");
		String excelName = excel.getExcelName();
		Assert.notBlank(excelName, "excel名称不能为空！");
		int excelType = excel.getExcelType();
		Assert.isTrue(excelType==0||excelType==1, "excel类型为导出、导入！");
		
		//校验单元格内容属性
		List<CellTemplate> cells = excel.getCell();
		if(cells.size()>0){
			for (CellTemplate cell : cells) {
				String field = cell.getField();
				Assert.notBlank(field, "对应的字段名不能为空！");
				String name = cell.getName();
				Assert.notBlank(name, "名称不能为空！");
			}
			
		}
		return true;
	}
	
	/**
	 * 设置response头消息
	 * @param response
	 * @param excelFileName
	 * @throws UnsupportedEncodingException
	 */
	private void setResponseHeader(HttpServletResponse response, String excelFileName) throws UnsupportedEncodingException {
		response.reset();
		response.setContentType("application/ms-excel;charset=gb2312");
		response.setHeader("Content-Disposition", "attachment; filename=\""
				+ new String(excelFileName.getBytes("gb2312"), "iso8859-1")
				+ new String("导入模板".getBytes("gb2312"), "iso8859-1") + ".xls\"");
	}
	
	/**
	 * 将excel写入到磁盘上
	 * @param excelName 
	 * @param workbook 
	 * @return
	 */
	private String doExportWriteExcelFile(String excelName, SXSSFWorkbook workbook) {
		FileOutputStream out = null;
		String nowTimeStr = String.valueOf(System.currentTimeMillis());
		//创建目录
		String baseDir = excelExportExcelDataTempDir + File.separator;
		File fileDriectory = new File(baseDir);
		if(!fileDriectory.exists()) {
			FileUtil.createDirectory(baseDir);
		}
		
		String excelFileName = baseDir + excelName + nowTimeStr +".xls";
		try {
			out = new FileOutputStream(excelFileName);
			workbook.write(out);//全部数据写到文件
			workbook.dispose();//删除临时文件
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("导出文件[{}]异常终止, 异常信息为:{}", excelName, e.getMessage());
			throw new ServiceException("导出文件失败");
		} finally {
			try {
				workbook.close();
				if(out != null) {
					out.close();
				}
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return excelFileName;
	}

	/**
	 * 将要导出的一列数据写到临时文件中
	 * @param workbook
	 * @param sheet
	 * @param excelRowNum
	 * @param template
	 * @param exportInvoker
	 */
	@SuppressWarnings("unused")
	private void doExportDatasToTempFile(SXSSFWorkbook workbook, Sheet sheet, AtomicInteger excelRowNum,
			ExportTemplate template, BatchExportInvoker exportInvoker) {
		while (exportInvoker.hasNext()) {
			Map<String,Object> dataMap = exportInvoker.next();
			Row row = sheet.createRow(excelRowNum.getAndIncrement());
			int colNum = DEAULT_START_COLUMN_NUMBER;//列的索引号
			XSSFCellStyle cellStyle = null;
			List<CellTemplate> cells = template.getCell();
			String borderStyle = template.getBorderStyle();
			XSSFColor borderColor = setBorderColorConfig(borderStyle);
			for (CellTemplate temp : cells) {
				String field = temp.getField();
				String style = temp.getStyle();
				boolean isShow = temp.isShow();
				Object value = dataMap.get(field);
				Cell cell = row.createCell(colNum++);
				//设置单元格的背景色
				if(StringUtils.isNotBlank(style)) {
					XSSFColor foregroundColor = setCellForeroundColorConfig(style);
					//获取excel的样式对象
					cellStyle = getCellStyle(workbook, borderColor, foregroundColor);
					cell.setCellStyle(cellStyle);
				}
				//设置单元格的值
				if (isShow) {//是否是空白列
	                    if (value != null) {
	                        if (value instanceof String) {
	                            if (StringUtils.isBlank((String) value)) {
	                                cell.setCellValue("-");
	                            } else {
	                                cell.setCellValue((String) value);
	                            }
	                        } else {
	                            cell.setCellValue(value.toString());
	                        }
	                    } else {
	                        cell.setCellValue("-");
	                    }
	           } else {
	                    //间隔列,不做处理
	                }
				
			}
			
		}
		
	}

	/**
	 * 将excel标题信息写到excel临时文件中
	 * @param workbook 
	 * @param sheet
	 * @param rowNum 自然列
	 * @param template excel模板
	 */
	private void doExportTitlesToTempFile(SXSSFWorkbook workbook, Sheet sheet, int rowNum,
			ExportTemplate template) {
		Assert.notNull(template, "导出excel模板不能为空!");
		/** 初始化Border样式 */
		String borderStyle = template.getBorderStyle();
		XSSFColor borderColor = setBorderColorConfig(borderStyle);
		/** 初始化行&单元格 */
		Row row = sheet.createRow(rowNum);
        Cell cell;
        int colNum = DEAULT_START_COLUMN_NUMBER;//列的索引号
        XSSFCellStyle cellStyle;
        List<CellTemplate> cells = template.getCell();
        Assert.isTrue(cells.size()!=0, "导出excel模板格式错误");
        for (CellTemplate temp : cells) {
			String name = temp.getName();
			String style = temp.getStyle();
			boolean isShow = temp.isShow();
			//获取单元格的样式
			XSSFColor foregroundColor = setCellForeroundColorConfig(style);
			//获取excel的样式对象
			cellStyle = getCellStyle(workbook, borderColor, foregroundColor);
			//创建单元格&设置样式&赋值
            cell = row.createCell(colNum++);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(!isShow ? "" : name);
		}
        try {
			((SXSSFSheet) sheet).flushRows();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 设置表格表框
	 * @param borderStyle
	 * @return
	 */
	private XSSFColor setBorderColorConfig(String borderStyle) {
		XSSFColor borderColor = null;
		if(StringUtils.isNoneBlank(borderStyle)) {
			String[] borderColorRGB = borderStyle.split(":");
			if(borderColorRGB.length >= 3 ) {
				borderColor = new XSSFColor(new Color(Integer.parseInt(borderColorRGB[0]),
						Integer.parseInt(borderColorRGB[1]), Integer.parseInt(borderColorRGB[2])));
			}
		}
		return borderColor;
	}

	/**
	 * 设置单元格背景
	 * @param style
	 * @return
	 */
	private XSSFColor setCellForeroundColorConfig(String style) {
		XSSFColor foregroundColor = null;
		String[] foregroundColorRGB = style.split(":");
        if (foregroundColorRGB.length >= 3) {
            foregroundColor = new XSSFColor(new Color(Integer.parseInt(foregroundColorRGB[0]),
                Integer.parseInt(foregroundColorRGB[1]), Integer.parseInt(foregroundColorRGB[2])));
        }
		return foregroundColor;
	}

	/**
     * 获取单元格样式
     * @param workbook
     * @param borderColor 边框颜色
     * @param foregroundColor 单元格填充颜色
     * @return
     */
    private XSSFCellStyle getCellStyle(SXSSFWorkbook workbook, XSSFColor borderColor, XSSFColor foregroundColor){
        XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.createCellStyle();
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        if(borderColor != null) {
        	cellStyle.setTopBorderColor(borderColor);
        	cellStyle.setBottomBorderColor(borderColor);
        	cellStyle.setLeftBorderColor(borderColor);
        	cellStyle.setRightBorderColor(borderColor);
        }
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        if(foregroundColor != null) {
        	cellStyle.setFillForegroundColor(foregroundColor);
        }
        return cellStyle;
    }
	
	/**
	 * 回调执行一列数据
	 * @author owen.he
	 *
	 */
	public static abstract class BatchExportInvoker {
        /**
         * 是否还有下一行数据
         * @return
         */
        public abstract boolean hasNext();

        /**
         * 导出数据行
         * @return 数据行
         */
        public abstract Map<String, Object> next();

    }
	
    /**
     * 文件
     * 导出数据
     * @author owen.he
     *
     */
    public interface DownloadFileInvoker {
        void invoke(String filePath);
    }
	
    /**
     * 输出流
     * 导出数据
     * @author owen.he
     *
     */
    public interface DownloadStreamInvoker {
        void invoke(SXSSFWorkbook workbook);
    }
}
